const SqliteUtil = appRequire('modules/SqliteUtil');
const SysUser = appRequire('model/desktop/SysUser');
const SysRole = appRequire('model/desktop/SysRole');
const SysAuth = appRequire('model/desktop/SysAuth');
const PageResultVo = appRequire('vo/PageResultVo.js');
class SysUserSequelizeDao {
  constructor() {

  }
  static async create() {
    await SysUser.sync();
  }
  static async saveOne(user) {
    try {
      await SysUser.create(user);
    } catch (error) {
      throw new Error(error);
    }
  }
  static async updateOne(user) {
    try {
      await SysUser.update(user,{where:{id:user.id}});
    } catch (error) {
      throw new Error(error);
    } 
  }
  static async deleteById(id) {
    try {
      await SysUser.destroy({where:{id}});
    } catch (error) {
      throw new Error(error);
    }
  }
  static async deleteMany(ids) {
    try {
      ids = ids.split(",");
      console.log(ids);
      await SysUser.destroy({where:{id:ids}});
    } catch (error) {
      throw new Error(error);
    }
  }
  static async findUserAuthItem(id) {
    try {
      let roleIdsObj = await SysUser.findOne({
        where:{
          id
        },
        raw:true,
        attributes:[["sys_user_role_ids","roleIds"]]
      });
      let roleIds = roleIdsObj.roleIds.split(",");
      let authIdsObj = await SysRole.findAll({
        where:{
          id:roleIds
        },
        raw:true,
        attributes:[["sys_role_auth_ids","authIds"]]
      });
      let authIds = authIdsObj.map(function(val){
        return val.authIds;
      });
      let authIdsStr ="";
      authIds.forEach(function(val,index){
        if(index!=authIds.length-1){
          authIdsStr+=val+","
        }else{
          authIdsStr+=val
        }
      });
      let authIdsArr = authIdsStr.split(",");
      let authIdsSet = new Set();
      authIdsArr.forEach(function(val){
        authIdsSet.add(val);
      });
      authIds = Array.from(authIdsSet);
      let authUrlsObj = await SysAuth.findAll({
        where:{
          auth_url:{
            [Op.ne]:''
          },
          id:authIds,
        },
        raw:true,
        attributes:[["auth_url","authUrl"]]
      });
      let authUrls = authUrlsObj.map(function(val){
        return val.authUrl;
      });
      let data ={};
      let authUrlsSet = new Set();
      authUrls.forEach(function(val){
        authUrlsSet.add(val);
      });
      data.authUrls = Array.from(authUrlsSet);
      let sql = `select sa.auth_name as authName,sa.auth_url as authUrl,sa.auth_icon as authIcon,
      sa.id,sa.pid 
      from sys_auth sa left join sys_auth_type st on sa.auth_type=st.id where st.type_name='系统菜单' and sa.id in (:authIds) order by sa.sort desc`
      let authMenus = await sequelize.query(sql,{
        replacements: {authIds},
        raw:true,
        type: QueryTypes.SELECT
      });
      data.authMenus = authMenus;
      return data;
    } catch (error) {
      throw new Error(error);
    } 
  }
  static async findUserCommonAuthItem(){
    try {
      let sql = `SELECT auth_url as authUrl FROM sys_auth where path<>'' and path like CONCAT((SELECT path FROM sys_auth where auth_name=:authName),'/%')`;
      let data = await sequelize.query(sql,{
        replacements: {authName:'公共授权'},
        raw:true,
        type: QueryTypes.SELECT
      });
      return data;
    } catch (error) {
      throw new Error(error);
    } 
  }
  static async findUserAuthItem_bk(id) {
    let db = await SqliteUtil.open(appConfig.dbPath);
    try {
      let roleIdsObj = await db.get(`select sys_user_role_ids as roleIds from sys_user where id = ?`,[id]);
      let roleIds = roleIdsObj.roleIds.split(",");
      roleIds = roleIds.map(function(val){
        return `'${val}'`
      });
      roleIds = roleIds.join(",");
      let sql = `select sys_role_auth_ids as authIds from sys_role where id in (${roleIds})`
      let authIdsObj = await SqliteUtil.each(db,sql);
      let authIds = authIdsObj.map(function(val){
        return val.authIds;
      });
      let authIdsStr ="";
      authIds.forEach(function(val,index){
        if(index!=authIds.length-1){
          authIdsStr+=val+","
        }else{
          authIdsStr+=val
        }
      });
      let authIdsArr = authIdsStr.split(",");
      let authIdsSet = new Set();
      authIdsArr.forEach(function(val){
        authIdsSet.add(val);
      });
      authIds = Array.from(authIdsSet);
      authIds = authIds.map(function(val){
        return `'${val}'`
      });
      authIds = authIds.join(",");
      console.log(authIds);
      sql = `select auth_url as authUrl from sys_auth where auth_url<>'' and id in (${authIds})`
      let authUrlsObj = await SqliteUtil.each(db,sql);
      let authUrls = authUrlsObj.map(function(val){
        return val.authUrl;
      });
      let data ={};
      data.authUrls = authUrls;
      sql = `select sa.auth_name as authName,sa.auth_url as authUrl,sa.auth_icon as authIcon,
      sa.id,sa.pid 
      from sys_auth sa left join sys_auth_type st on sa.auth_type=st.id where st.type_name='系统菜单' and sa.id in (${authIds}) order by sa.sort desc`
      let authMenus = await SqliteUtil.each(db,sql);
      data.authMenus = authMenus;
      // console.log(data);
      return data;
    } catch (error) {
      throw new Error(error);
    } finally {
      await db.close();
    }
  }
  static async findOneByUsernameAndPassword(user) {
    try {
      let data = await SysUser.findAll({
        where:{
          username:user.username,
          password:user.password
        },
        plain:true,
        raw:true,
      });
      return data;
    } catch (error) {
      throw new Error(error);
    } 
  }
  static async findOne(id) {
    try {
      let data = await SysUser.findOne({
        where:{
          id
        },
        raw:true,
        attributes: { exclude: ['password'] },
      });
      return data;
    } catch (error) {
      throw new Error(error);
    } 
  }
  static async findById(id) {
    try {
      let data = await SysUser.findOne({
        where:{
          id
        },
        raw:true,
        attributes: { exclude: ['password'] },
      });
      return data;
    } catch (error) {
      throw new Error(error);
    } 
  }
  static async findAll() {
    try {
      let data = await SysUser.findAll({
        raw:true,
        attributes: { exclude: ['password'] },
      });
      return data;
    } catch (error) {
      throw new Error(error);
    } 
  }
  static async findByPage(page) {
    try {
      let offset = (page.pageNum-1)*page.pageSize;
      let limit = page.pageSize;
      let where = {};
      //动态拼装sql的where条件
      if(page.searchParams&&page.searchParams.username){
        where.username = {
          [Op.like]: `%${page.searchParams.username}%`,
        }
      }
      let result = await SysUser.findAndCountAll({
        where,offset,limit,raw:true,attributes:{exclude:['password']},order:[["create_time","DESC"]]
      })
      console.log(result)
       return PageResultVo.success('success',result.count,result.rows);
    } catch (error) {
      throw new Error(error);
    }
  }
  /**
   * 调用原生方法
   * @param {*} page 
   */
  static async findByPage_bk(page) {
    try {
      let sqlWhere = '';
      let sqlParams = [(page.pageNum-1)*page.pageSize,page.pageSize];
      if(page.searchParams&&page.searchParams.username){
        sqlWhere +='where username=?'
        sqlParams.unshift(page.searchParams.username);
      }
      let result = await sequelize.query(`select count(*) as totalNum from sys_user ${sqlWhere}`,{ type: QueryTypes.SELECT,plain:true,raw:true});
      console.log(result);
      let count = result.totalNum;
      let data = await sequelize.query(`select id,username,nick_name as nickName,phone_num as phoneNum,sort from sys_user ${sqlWhere} order by create_time desc limit ?,?`, {
        replacements: sqlParams,
        model: SysUser,
        // plain:true, //plain为true 返回一个对象
        raw:true,
        type: QueryTypes.SELECT
      });
      console.log(count);
      console.log(data);
      //加了await 会等待data拼装完成
      return PageResultVo.success('success',count,data);
    } catch (error) {
      throw new Error(error);
    }
  }
}
module.exports = SysUserSequelizeDao;